package com.tanhua.manage.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tanhua.common.pojo.UserInfo;
import com.tanhua.manage.pojo.AgeResult;
import com.tanhua.manage.pojo.GenderDistribution;
import com.tanhua.manage.pojo.IndustryDistribution;
import com.tanhua.manage.pojo.LocalDistribution;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

public interface UserInfosMapper extends BaseMapper<UserInfo> {
    @Select("select industry as title,count(id) as amount from tb_user_info " +
            "WHERE created BETWEEN #{startTime} AND #{endTime} group by industry "
    )
    List<IndustryDistribution> queryIndustry (@Param("startTime") Date startDate, @Param("endTime") Date endDate);

    @Select("SELECT \n" +
            " SUM(CASE WHEN age <18 OR age IS NULL THEN 1 ELSE 0 END) AS 'age1',\n" +
            " SUM(CASE WHEN age BETWEEN 18 AND 24 THEN 1 ELSE 0 END) AS 'age2',\n" +
            " SUM(CASE WHEN age BETWEEN 24 AND 30 THEN 1 ELSE 0 END) AS 'age3',\n" +
            " SUM(CASE WHEN age BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 'age4',\n" +
            " SUM(CASE WHEN age BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 'age5',\n" +
            " SUM(CASE WHEN age>50 THEN 1 ELSE 0 END) AS 'age6'\n" +
            " FROM tb_user_info  WHERE created BETWEEN #{startTime} AND #{endTime} ")
    AgeResult queryAge(@Param("startTime") Date  startDate, @Param("endTime") Date endDate);

    @Select("SELECT sex AS title,COUNT(id) AS amount FROM tb_user_info WHERE created BETWEEN #{startTime} AND #{endTime} GROUP BY sex")
    List<GenderDistribution> queryGender(@Param("startTime") Date  startDate, @Param("endTime") Date endDate);

    @Select("SELECT city AS title,COUNT(id) AS amount FROM tb_user_info  WHERE created BETWEEN #{startTime} AND #{endTime} GROUP BY city ")
    List<LocalDistribution> queryCity(@Param("startTime") Date  startDate, @Param("endTime") Date endDate);

}